﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using CommonClasses;

namespace QC
{
    public class Measurement: IMeasurements
    {
       
        //school
        string connectionString = @"Data Source=t-sql;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True";
        //Patricia's laptop
        //string connectionString = @"Data Source=MYLAPTOP;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=True";
        //Beth home
        //string connectionString = @"Data Source=BETH-HP;Initial Catalog=Measurement;Integrated Security=True";

        // inserts row into Measurements table of Measurements DB
        #region properties

        Log logging = new Log();

        private int _materialID;
        private decimal _measurement;
        private int _materialattribID;
        private int _orderID;

        public int OrderID { get { return _orderID; } set { this._orderID = value; } }
        public int MaterialID { get { return _materialID; } set { this._materialID = value; } }
        public decimal MeasurementTaken { get { return _measurement; } set { this._measurement = value; } }
        public int MaterialattribID { get { return _materialattribID; } set { this._materialattribID = value; } }

        private string daysfortesting = ""; 
        private int timestaken = 0;
        private int timesshouldbetaken = 0;
        private List<DateTime> testdayslist = new List<DateTime>();
        bool wasdelayed = false;
        string delay = string.Empty;
        DateTime date = DateTime.Now;//gets date in mm/dd/yyyy format

        #endregion

        public static bool IsNumeric(System.Data.DataColumn col)
        {
            if (col == null)
                return false;
            // Make this const
            var numericTypes = new[] { typeof(Byte), typeof(Decimal), typeof(Double),
        typeof(Int16), typeof(Int32), typeof(Int64), typeof(SByte),
        typeof(Single), typeof(UInt16), typeof(UInt32), typeof(UInt64)};
            return numericTypes.Contains(col.DataType);
        }

        //to fill combo box to select material can be displayed by order id or material id
        public DataTable ListofMaterialsScheduled()
        {
            
            DataTable dt = new DataTable("orderlist");
            SqlConnection connection = new SqlConnection(connectionString);
           
            using (SqlCommand SelectCommand = new SqlCommand(
                 "SELECT * FROM [Order].[dbo].[Order] WHERE getDate() BETWEEN StartDate AND EndDate", connection))
            {
                connection.Open();
                dt.Load(SelectCommand.ExecuteReader());
            }
            
            return dt;
        }//end ListofMaterialsScheduled



        //to fill datagrid for measurements of the item selected in the combo box (see above method)
        ////http://brentedwards.net/2012/03/11/wpf-datagridrow-double-click-with-mvvm/
        //to make column editable on double click to enter measurements in datagrid?
        //and s.Hour like '%"+ hourofday +"%'

        public DataTable DisplayRequiredMeasurements(int materialID)
        {
            string weekday = date.DayOfWeek.ToString();
            string hourofday = date.Hour.ToString();
            _materialID = materialID;
            DataTable dt = new DataTable("materialstomeasure");
            SqlConnection connection = new SqlConnection(connectionString);

            using (SqlCommand SelectCommand = new SqlCommand(
                 "SELECT  o.OrderID, o.[StartDate],  o.[EndDate], s.MaterialID, qca.[QCAttribID],  m.MaterialName, s.[Hour],s.[Day] , qca.[QCAttribName] " +
                 "FROM [Order].[dbo].[Order] o " +
                 "INner JOIN [QCConfigurationManager].[dbo].[QCSchedule] s " +
                 "ON s.[MaterialID] = o.[MaterialID] " +
                 "INNER JOIN [QCConfigurationManager].[dbo].[Material] m " +
                 "ON s.[MaterialID] = m.[MaterialID] " +
                 "INNER JOIN [QCConfigurationManager].[dbo].[MaterialQCAttrib] mqca  " +
                 "ON mqca.[MaterialID] = m.[MaterialID] " +
                 "INNER JOIN [QCConfigurationManager].[dbo].[QCAttrib] qca  " +
                 "ON mqca.[QCAttribID] = qca.[QCAttribID] " +
                 "WHERE s.[MaterialID] = '" + _materialID + "' and s.[QCAttribID] = qca.[QCAttribID] " +
                   " and s.Day like '%"+weekday+"%' " 
                 , connection))
            {
                connection.Open();
                dt.Load(SelectCommand.ExecuteReader());
                dt.Columns.Add(new DataColumn("EnterMeasurement", typeof(decimal)));
                //dt.Columns.Add(new DataColumn("MachineID", typeof(Int32)));
                dt.Columns.Add(new DataColumn("ScheduledDate", typeof(String)));
              
            }
            //display date/time to be measured
            foreach (DataRow row in dt.Rows)
            {
                foreach (DataColumn column in dt.Columns)
             {
              string testday = WhenShouldTestBeTaken(Convert.ToDateTime(dt.Rows[0]["StartDate"]), Convert.ToDateTime(dt.Rows[0]["EndDate"]), dt.Rows[0]["day"].ToString(), dt.Rows[0]["Hour"].ToString());
               HasTestBeenTakenYet(Convert.ToInt32(row["OrderID"]), Convert.ToInt32(row["MaterialID"]), Convert.ToInt32(row["QCAttribID"]));
               testday.Substring(daysfortesting.IndexOf(',') + 1, timestaken);
               string[] datetocompare_array = testday.Split(',');
               string datetocompare = datetocompare_array[timestaken];
               row.SetField("ScheduledDate", datetocompare);
            }

            }
            //check to see if scheduleddate is greater than today's date and remove if it is.
            foreach (DataRow dr in dt.Rows)
            {
                if (Convert.ToDateTime( dr["ScheduledDate"]) > date)
                    dr.Delete();
            }
            dt.AcceptChanges();

            return dt;
        }//end DisplayRequiredMeasurements


        //get measurement information from tester entered into screen
        public string GetMeasurementsTaken_table(DataTable table)
        {
            string results = "";
            foreach (DataRow row in table.Rows)
            {

                _materialID = Convert.ToInt32(row["MaterialID"]); //materialID 
                _measurement = Convert.ToDecimal(row["EnterMeasurement"]);
                _materialattribID = Convert.ToInt32(row["QCAttribID"]);
                _orderID = Convert.ToInt32(row["OrderID"]);
                               
                
                bool passfail = CalculateMeasurementdifference(_measurement, _materialattribID, _materialID);

                int empid = 1234567; //will need to get from session? should be passed from UI?
                int shift = 0; //have to get session information (need plant from group?) to get what shift
                int crew = 0; //have to get session information (plant) to get what shift

                wasdelayed =  WasMeasureDelayed(_orderID, _materialattribID, _materialID, date);
                bool ot = wasdelayed;
                string delayed = delay;

                try
                {
                    
                    string message = SaveMeasurementstoDB(_orderID, _materialID, _materialattribID, _measurement, date, empid, shift, crew, passfail, ot, delayed);
                    results = message; //+ " was delayed =" + ot;
                }
                catch (System.Data.SqlClient.SqlException sqlException)
                {
                    results = sqlException.Message;
                    int eid = 123456;//test eid
                    string severity = "testingseverity";
                    string logresults = logging.LogTransaction(eid, results, results, severity);
                }//end catch
               
            }//end datarow foreach
            return results;
        }//

  

        public bool WasMeasureDelayed(int orderID, int materialattribID, int materialID, DateTime date)
        {
            _materialID = materialID;
            _materialattribID = materialattribID;
            _orderID = orderID;

            string day = "";
            string hour = "";
            DateTime startDate;
            DateTime endDate;
            DataTable dt = new DataTable();
            DateTime datetaken = date;


            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand SelectCommand = new SqlCommand(
                  "Select s.[Day] , s.[Hour] , o.[StartDate],  o.[EndDate], o.[OrderID] "
                + " from [QCConfigurationManager].[dbo].[QCSchedule] s,"
                + " [Order].[dbo].[Order] o"
                + " where s.[MaterialID]  = '" + _materialID + "'"
                + "and s.[QCAttribID] = '" + _materialattribID + "'"
                , connection);

                {
                    connection.Open();
                    dt.Load(SelectCommand.ExecuteReader());
                }

            }//end using

            day = dt.Rows[0]["Day"].ToString();
            hour =dt.Rows[0]["Hour"].ToString();
            startDate = Convert.ToDateTime(dt.Rows[0]["StartDate"]);
            endDate = Convert.ToDateTime(dt.Rows[0]["EndDate"]);


           // day = whatDayofWeektoTest(day);
            string daysfortesting = WhenShouldTestBeTaken(startDate, endDate, day, hour);
            HasTestBeenTakenYet(_orderID, _materialID, _materialattribID);
            daysfortesting.Substring(daysfortesting.IndexOf(',')+1,timestaken);

            string[] datetocompare_array = daysfortesting.Split(',');
            string datetocompare = datetocompare_array[timestaken];
            //get test date # after number of tests taken (so if taken once already, get the second date the test should be take)
            DateTime datetocompare_date = Convert.ToDateTime(datetocompare);

            if (datetaken > datetocompare_date)
            { wasdelayed = true; }

            TimeSpan lengthdelay = datetaken.Subtract(datetocompare_date);
            delay = lengthdelay.Days.ToString() + " days " + lengthdelay.Hours.ToString() + " hours " + lengthdelay.Minutes.ToString() + " mins";

            return wasdelayed;
        }


        //-----------------------check to see if test has been taken, and if how many times--------------------------------------------
        //which required test date to compare to...need to know if and how many times it has been taken already
        public string HasTestBeenTakenYet(int orderID, int materialID, int materialattribID)
        {

            _materialID = materialID;
            _materialattribID = materialattribID;
            _orderID = orderID;
            string results = "";

            //DateTime? datetaken;
            DataTable dt = new DataTable();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand SelectCommand = new SqlCommand(
                  "Select DateTaken "
                + " from [Measurement].[dbo].[Measurement]"
                + " where [MaterialID]  = '" + _materialID + "'"
                + " and [MeasurementAttribID] = '" + _materialattribID + "'"
                + " and [OrderID] = '" + _orderID + "'"
                , connection);

                {
                    connection.Open();
                    dt.Load(SelectCommand.ExecuteReader());
                    //datetaken = Convert.ToDateTime((SelectCommand.ExecuteScalar().ToString()));
                }

            }//end using

            
            if (dt != null)
                if (dt.Rows.Count > 0)
                { results = Convert.ToString(dt); }
            timestaken = dt.Rows.Count;

            return "Test taken: " + timestaken + " on " + results;

        }// end 

        //-------------gets list of date and times that measurement should be taken ----------------------

        public string WhenShouldTestBeTaken(DateTime start, DateTime end, string day, string hour)
        {
            List<DateTime> testdayslist = new List<DateTime>();
            string[] days = day.Split(',');
            int[] times = hour.Split(',').Select(n => Convert.ToInt32(n)).ToArray();
            string testdays = "";
            //int hour = 1;
            //string day = "W";


            try //get list of days to test between start and end date
            {
                //get number of days between start and end dates
                var totalDays = (int)end.Subtract(start).TotalDays;

                if (totalDays != 0)// if length of run longer than one day
                {
                    //get list of all dates between start and end day
                    var allDatesQry = from d in Enumerable.Range(1, totalDays)
                                      select new DateTime(
                                                    start.AddDays(d).Year,
                                                    start.AddDays(d).Month,
                                                    start.AddDays(d).Day);
                    //And extracting those weekdays we explicitly wanted to return (ex. all the wednesdays)
                    foreach (string item in days)
                    {
                        var selectedDatesQry = from d in allDatesQry
                                               where day.Contains(Convert.ToString(d.DayOfWeek))
                                               select d;
                        testdayslist = selectedDatesQry.ToList();
                    };

                    timesshouldbetaken = testdayslist.Count;

                    //add  time of day it should be taken to date 
                    foreach (int item in times)
                    {
                        foreach (DateTime x in testdayslist)
                        {
                            DateTime datex = x.Date; //get rid of time
                            datex = datex.Date.Add(new TimeSpan(item, 0, 0));//add in time test should be taken
                            testdays = testdays + string.Join(", ", datex) + ",";
                        }
                    }

                }
            }
            catch (Exception ex)
            {
                testdays = ex.Message;
                
               // testing logging class
                int eid = 123456;//test eid
                string severity = "testingseverity";
                string logresults = logging.LogTransaction(eid, testdays, testdays, severity);

            }
            return testdays; //for display, use testdayslist for calcs
        }


        //calculate the difference between the measurements taken and specs
        //returns difference to form , return boolean pass/fail
        public bool CalculateMeasurementdifference(decimal measurement, int materialattribID, int materialID)
        {
            bool result = false;

            _materialID = materialID;
            _measurement = measurement;
            _materialattribID = materialattribID;

            DataTable dt = new DataTable();
            SqlConnection connection = new SqlConnection(connectionString);
            using (SqlCommand SelectCommand = new SqlCommand(
                 "SELECT * from [QCConfigurationManager].[dbo].[MaterialQCAttrib] " +
                 "WHERE [QCAttribID] = '" + materialattribID + "' and [MaterialID] = '" + materialID + "' " +
                 " and [MeasurementType] = 'UpperLower' "+
                 "and " + _measurement + " BETWEEN [Upper] and [Lower]" +
                 " and [MaterialQCAttribDisabled] = 0" +
                 " Union All " +
                 "SELECT * from [QCConfigurationManager].[dbo].[MaterialQCAttrib] " +
                 "WHERE [QCAttribID] = '" + materialattribID + "' and [MaterialID] = '" + materialID + "' " +
                 " and [MeasurementType] = 'TargetThreshold' "+
                 "and " + _measurement + " BETWEEN [Target]-[Threshold]*[Target] and [Target]+[Threshold]*[Target]" +
                 " and [MaterialQCAttribDisabled] = 0"
                 , connection))
            {
                connection.Open();
                dt.Load(SelectCommand.ExecuteReader());

            }

            if (dt != null)
                if (dt.Rows.Count > 0)
                { result = true; }

            return result;

        }

        public string SaveMeasurementstoDB 
                  (int orderID, int matid, int measureattbid, decimal value, DateTime date, int empid, int shift, int crew, bool pf, bool ot, string delayed)
        {
            string result = "";

            SqlConnection connection = new SqlConnection(connectionString);
            try
            {
                using (SqlCommand insertCommand = new SqlCommand(
                "INSERT INTO Measurement.dbo.Measurement VALUES"
                  + "(@OrderID, @MaterialID, @MeasurementAttribID,@Value, @DateTaken,  @EmpID, @Shift, @Crew, @Pass_Fail,@OnTime, @Delayed)"
                  , connection))
                {
                    insertCommand.Parameters.AddWithValue("@OrderID", orderID);
                    insertCommand.Parameters.AddWithValue("@MaterialID", matid);
                    insertCommand.Parameters.AddWithValue("@MeasurementAttribID", measureattbid);
                    insertCommand.Parameters.AddWithValue("@Value", value);
                    insertCommand.Parameters.AddWithValue("@DateTaken", date);
                    insertCommand.Parameters.AddWithValue("@EmpID", empid);
                    insertCommand.Parameters.AddWithValue("@Shift", shift);
                    insertCommand.Parameters.AddWithValue("@Crew", crew);
                    insertCommand.Parameters.AddWithValue("@Pass_Fail", pf);
                    insertCommand.Parameters.AddWithValue("@OnTime", ot);
                    insertCommand.Parameters.AddWithValue("@Delayed", delayed);

                    insertCommand.Connection = connection;
                    connection.Open();
                    insertCommand.ExecuteNonQuery();
                    result = "Success! row added to Measurement table";
                    int eid = 123456;//test eid
                    string severity = "testingseverity";
                    string logresults = logging.LogTransaction(eid, result, result, severity);
                }
            }//end try
            catch (System.Data.SqlClient.SqlException sqlException)
            {
                result = sqlException.Message;
                int eid = 123456;//test eid
                string severity = "testingseverity";
                string logresults = logging.LogTransaction(eid, result, result, severity);
            }//end catch

            return result; //+ "" + logresults;
        }// end SaveMeasurementstoDB


        //if textboxes
        //gathers information to send to measurement database
        public string GetMeasurementsTaken(decimal measurement, int orderID, int materialattribID, int materialID)
        {
            //    _materialID = materialID;
            //    _measurement = measurement;
            //    _materialattribID = materialattribID;
            //    _orderID = orderID;
            string results = "";
            //    DateTime date = DateTime.Now;//gets date in mm/dd/yyyy format
            //    bool passfail = CalculateMeasurementdifference(measurement, materialattribID, materialID);


            //    int empid = 0; //will need to get from session? should be passed from UI?
            //    int shift = 0; //have to get session information (need plant from group?) to get what shift
            //    int crew = 0; //have to get session information (plant) to get what shift

            //   // WasMeasureDelayed(orderID, materialattribID, materialID, date);
            //    bool ot = wasdelayed;
            //    string delayed = delay;

            //    try
            //    {

            //        string message = SaveMeasurementstoDB(orderID, materialID, materialattribID, measurement, date, empid, shift, crew, passfail, ot, delayed);
            //        results = message + " was delayed =" + ot;
            //    }
            //    catch (System.Data.SqlClient.SqlException sqlException)
            //    {
            //        results = sqlException.Message;
            //        //int eid = 123456;//test eid
            //        //string severity = "testingseverity";
            //      //  string logresults = LogTransaction(eid, results, results, severity);
            //    }//end catch

            return results;
        }//
    }
}

